================================================================================
TEPS REGISTRATION CORE — DATABASE SCHEMA REFERENCE
================================================================================

Contributors: Mikal Farley
Created:      April 2026
Last Updated: August 5, 2026

Database Engine: SQLite 3 (C API via `import SQLite3`)
Location:        ~/Library/Application Support/TEPS Registration Core/registration.sqlite
PRAGMAs:         journal_mode=WAL, foreign_keys=ON
Actor:           DatabaseService (Swift actor — all access serialized)


================================================================================
1. TABLES
================================================================================


--- events -------------------------------------------------------------------

  Stores event sessions. Currently one active event at a time (the last one
  created). An "event" represents a single operating session — typically one
  day's photo event, but could span multiple days.

  CREATE TABLE events (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      name            TEXT NOT NULL,
      date            TEXT NOT NULL,           -- "yyyy-MM-dd"
      api_source_url  TEXT,                    -- remote API source (future use)
      synced_at       TEXT,                    -- ISO 8601 last sync timestamp
      event_code      TEXT                     -- cloud event code this row belongs to
  );

  event_code binds a local event to the cloud event whose code sync sends as
  the X-Event-Code header. NULL means "not yet stamped" — every database
  created before this column reaches sync that way exactly once and adopts the
  code in use. Two questions are deliberately kept apart:

    getActiveEvent()              — the newest local row. What this machine is
                                    showing right now. ~40 callers.
    activeEvent(forEventCode:)    — the event bound to a code. What sync is
                                    allowed to push to. Sync only.

  Before this column existed the two were the same query, so re-pointing a
  station at a different event code kept the previous event's registrations and
  pushed them under the new code, where the server refused every one.


--- registrations ------------------------------------------------------------

  The core table. One row per customer registration per event. Contains
  contact info, workflow state, photo code, and upload tracking flags.

  CREATE TABLE registrations (
      id                  INTEGER PRIMARY KEY AUTOINCREMENT,
      event_id            INTEGER NOT NULL,
      sync_id             TEXT,                -- UUID for cross-device matching
      source_type         INTEGER DEFAULT 1,   -- 0=reservation, 1=walkin_local, 2=walkin_web
      code                TEXT,                -- photo code (e.g. "0411A3BF7K2P")
      first_name          TEXT NOT NULL,
      last_name           TEXT NOT NULL,
      email               TEXT,
      phone               TEXT,                -- digits only (normalized on input)
      child_name          TEXT,                -- semicolon-delimited for multi-child
      child_wishlist      TEXT,                -- semicolon-delimited
      child_age           TEXT,                -- semicolon-delimited
      elf_name            TEXT,                -- assigned elf name
      postal_code         TEXT,
      street_address      TEXT,
      date_of_birth       TEXT,
      age_range           TEXT,
      group_size          INTEGER DEFAULT 1,
      optin               INTEGER DEFAULT 0,   -- legacy single opt-in flag
      optins              TEXT,                -- JSON string: {"marketing": true, "sms": false}
      notes               TEXT,                -- freeform or "key: value\n" format
      order_text          TEXT,                -- display text for orders
      promo_code          TEXT,
      custom_fields       TEXT,                -- JSON string: {"shirt_size": "M", ...}
      pet_type            TEXT,
      pet_breed           TEXT,
      status              TEXT NOT NULL DEFAULT 'registered',
      current_station     TEXT,                -- current station type (host, camera, etc)
      reservation_time    TEXT,                -- HH:mm:ss or ISO 8601
      is_late             INTEGER DEFAULT 0,
      fastpass            INTEGER DEFAULT 0,
      fastpass_status     TEXT,
      email_uploaded      INTEGER DEFAULT 0,   -- 1 = email data sent to cloud
      userdata_uploaded   INTEGER DEFAULT 0,   -- 1 = user data sent to cloud
      text_uploaded       INTEGER DEFAULT 0,   -- 1 = SMS data sent to cloud
      data_declined       INTEGER DEFAULT 0,   -- 1 = customer declined to provide info
      created_at          TEXT NOT NULL,        -- ISO 8601
      updated_at          TEXT NOT NULL,        -- ISO 8601

      FOREIGN KEY (event_id) REFERENCES events(id)
  );

  Status values (workflow order):
    "registered"     — Initial state. Customer exists but hasn't checked in.
    "checked_in"     — At the host station (front desk).
    "elf_processed"  — Processed by the elf station (if enabled).
    "photographed"   — At the camera station.
    "previewing"     — At the preview station.
    "checkout"       — At the POS station.
    "complete"       — Finished. Customer has left.
    "cancelled"      — Cancelled (can happen at any stage).
    "no_show"        — Marked as no-show (auto or manual).

  Multi-child convention:
    child_name, child_wishlist, and child_age use semicolons as delimiters.
    Example: child_name = "Alice;Bob;Charlie"
    This is a legacy Bamboo convention preserved for compatibility.
    At the sync boundary, these are converted to/from JSON arrays.

  Source type codes:
    0 = reservation  (booked online, synced from cloud)
    1 = walkin_local  (registered in-person via host station)
    2 = walkin_web    (self-registered via /registration page)


--- orders -------------------------------------------------------------------

  One order per registration. Tracks pricing, package selection, and POS
  processing status. Created when the customer reaches the POS workflow.

  CREATE TABLE orders (
      id                  INTEGER PRIMARY KEY AUTOINCREMENT,
      registration_id     INTEGER NOT NULL,
      order_status        INTEGER DEFAULT 0,
      santa_slap          INTEGER DEFAULT 0,   -- special pose/overlay count
      subtotal            REAL DEFAULT 0,
      tax                 REAL DEFAULT 0,
      total               REAL DEFAULT 0,
      discount            REAL DEFAULT 0,
      promo_code          TEXT,
      notes               TEXT,
      order_text          TEXT,
      custom_fields       TEXT,
      fastpass            INTEGER DEFAULT 0,
      fastpass_status     TEXT,
      last_action         TEXT,                -- last POS action performed
      quantity            INTEGER DEFAULT 1,
      created_at          TEXT NOT NULL,        -- ISO 8601
      updated_at          TEXT NOT NULL,        -- ISO 8601

      FOREIGN KEY (registration_id) REFERENCES registrations(id)
  );

  Order status codes:
    0 = New (just created)
    1 = Elf-processed
    2 = Photo-processed
    3 = POS-processed (checkout complete)
    9 = Cancelled


--- order_packages -----------------------------------------------------------

  Line items within an order. Each row is a package or addon selected by the
  customer during checkout.

  CREATE TABLE order_packages (
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      order_id    INTEGER NOT NULL,
      title       TEXT NOT NULL,               -- package display name
      code        TEXT,                        -- internal package code
      price       REAL DEFAULT 0,
      is_addon    INTEGER DEFAULT 0,           -- 1 = addon, 0 = base package

      FOREIGN KEY (order_id) REFERENCES orders(id)
  );


--- stations -----------------------------------------------------------------

  Station device registry. Currently used for future device tracking. Not
  heavily utilized in the current version.

  CREATE TABLE stations (
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      name        TEXT NOT NULL,
      type        TEXT NOT NULL,                -- host, elf, camera, preview, checkout
      device_id   TEXT,                         -- unique device identifier
      is_active   INTEGER DEFAULT 1
  );


--- queue_log ----------------------------------------------------------------

  The audit trail for all station transitions. Every time a customer enters,
  starts at, or exits a station, a row is written here. This is the source
  of truth for queue ordering and active/waiting state.

  CREATE TABLE queue_log (
      id                  INTEGER PRIMARY KEY AUTOINCREMENT,
      registration_id     INTEGER NOT NULL,
      sync_id             TEXT,                -- UUID for cross-device matching
      station_type        TEXT NOT NULL,        -- host, elf, camera, preview, checkout
      action              TEXT NOT NULL,        -- entered, started, exited
      actor_station_id    INTEGER,             -- which station device triggered this
      timestamp           TEXT NOT NULL,        -- ISO 8601

      FOREIGN KEY (registration_id) REFERENCES registrations(id)
  );

  Action values:
    "entered"  — Customer arrived at this station's queue (waiting).
    "started"  — Operator activated this customer (now being served).
    "exited"   — Customer left this station (moved to next or completed).

  Queue ordering logic (in getQueueForStation):
    Customers at a station are ordered by: active first (has "started" but
    no subsequent "exited"), then by "entered" timestamp. This gives operators
    the "Next" list with the active customer always at top.


================================================================================
2. INDEXES
================================================================================

  -- Registration lookups
  CREATE INDEX idx_registrations_event    ON registrations(event_id);
  CREATE INDEX idx_registrations_status   ON registrations(status);
  CREATE INDEX idx_registrations_station  ON registrations(current_station);
  CREATE INDEX idx_registrations_code     ON registrations(code);

  -- Sync matching (unique — one sync_id per record)
  CREATE UNIQUE INDEX idx_registrations_sync_id ON registrations(sync_id);
  CREATE UNIQUE INDEX idx_queue_log_sync_id     ON queue_log(sync_id);

  -- Order lookups
  CREATE INDEX idx_orders_registration    ON orders(registration_id);
  CREATE INDEX idx_orders_status          ON orders(order_status);
  CREATE INDEX idx_order_packages_order   ON order_packages(order_id);

  -- Queue log lookups
  CREATE INDEX idx_queue_log_registration ON queue_log(registration_id);
  CREATE INDEX idx_queue_log_station      ON queue_log(station_type);


================================================================================
3. COLUMN MIGRATIONS
================================================================================

  Migrations run on every app launch via migrateColumnsSync(). Each is an
  ALTER TABLE ADD COLUMN statement. SQLite silently ignores duplicate columns,
  so these are safe to run repeatedly.

  This approach means:
    - New columns are added by appending to the migration list
    - Old databases automatically gain new fields on next launch
    - No migration version tracking needed
    - Default values handle NULL for existing rows

  Current migration list (registrations table):
    email, phone, child_name, postal_code, street_address, date_of_birth,
    age_range, group_size, optin, optins, notes, order_text, promo_code,
    custom_fields, status, current_station, reservation_time, is_late,
    fastpass, fastpass_status, email_uploaded, userdata_uploaded,
    text_uploaded, data_declined, elf_name, child_wishlist, child_age,
    pet_type, pet_breed, sync_id, source_type

  Queue log migration:
    sync_id

  Events migration:
    event_code

  ⚠ readEvent() reads events by column POSITION (0-5), so event_code must be
  the last column in both CREATE TABLE and the migration list. Appending keeps
  a migrated database and a freshly-created one in the same order; inserting
  anywhere else silently shifts every field on one of them.


================================================================================
4. SYNC ID BACKFILL
================================================================================

  On startup, backfillSyncIdsSync() generates UUID sync_ids for any existing
  rows that have NULL sync_id. This ensures all records can participate in
  cloud sync even if they were created before sync support was added.

  Runs for both: registrations and queue_log tables.


================================================================================
5. RELATIONSHIPS (ER DIAGRAM)
================================================================================

  events
    |
    +-- 1:N -- registrations
                  |
                  +-- 1:1 -- orders
                  |            |
                  |            +-- 1:N -- order_packages
                  |
                  +-- 1:N -- queue_log

  - Each registration belongs to one event.
  - Each registration has at most one order.
  - Each order has zero or more packages.
  - Each registration has many queue_log entries (one per station transition).


================================================================================
6. ENRICHED FIELDS (NOT STORED IN DB)
================================================================================

  These fields exist on the Registration model but are computed by the server,
  not stored in the database. They are included in JSON API responses.

  status_display    String    Human-readable status label ("Checked In", etc).
                              Set by Registration.displayName(for:).

  structured_notes  Dict      Parsed key:value pairs from the notes field.
                              Set by Registration.parseNotes().

  is_queue_active   Bool      Whether the operator has clicked "Start" for
                              this customer at their current station. Derived
                              from queue_log (has "started" with no subsequent
                              "exited" at the current station).
                              Set by getQueueForStation() only.


================================================================================
7. KEY QUERIES
================================================================================

  Get active event:
    SELECT * FROM events ORDER BY id DESC LIMIT 1

  Get queue for station (e.g. "camera"):
    SELECT r.*, (EXISTS subquery for "started" action) AS is_queue_active
    FROM registrations r
    WHERE r.event_id = ? AND r.current_station = ?
    ORDER BY is_queue_active DESC, r.created_at ASC

  Status counts:
    SELECT status, COUNT(*) FROM registrations WHERE event_id = ? GROUP BY status

  Queue counts:
    SELECT current_station, COUNT(*) FROM registrations
    WHERE event_id = ? AND current_station IS NOT NULL AND status NOT IN ('complete','cancelled','no_show')
    GROUP BY current_station

  Generate next code:
    Built from RegistrationConfig.photoCodeSegments. Sequential segments use
    nextSequentialCounter() which scans existing codes to find the max.

  Upsert from sync:
    INSERT OR UPDATE matched on sync_id. If sync_id exists, update all fields.
    If not, insert a new row. Used by SyncService for cloud sync.


================================================================================
8. DATA LIFECYCLE
================================================================================

  Creation:
    - POST /api/registrations creates a registration with status "registered"
    - POST /api/test creates a test registration with code "__TEST__"
    - SyncService.upsertFromSync() creates/updates from cloud data

  Workflow transitions:
    - /checkin: registered -> checked_in + queue_log "entered" at host
    - /start: logs "started" at current station (no status change)
    - /advance: exits current station, enters next (or marks "complete")
    - /return: exits current station, re-enters previous
    - /cancel: sets status to "cancelled", clears current_station

  Deletion:
    - deleteTestRegistrations(): removes __TEST__ registrations
    - deleteAllEventData(): cascading delete of all data for an event
      (order_packages -> orders -> queue_log -> registrations)
    - No soft-delete — cancelled is a status, not deletion

  Upload flags:
    - email_uploaded, userdata_uploaded, text_uploaded are set to 1
      after successful upload to cloud services
    - Upload queue views show registrations where flag = 0


================================================================================
END OF DOCUMENT
================================================================================
